package com.buba.util;

import com.buba.goods.annotation.ExcelCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Excel工具类
 */
public class MyExcel<T> {
    /**
     * 导出Excel
     *
     * @param excelName 导出页名字
     * @param dataList  导出的数据
     * @param c         要导出的对象类
     * @param response  下载需要的响应对象
     * @throws Exception
     */
    public void exportExcel(String excelName, List<T> dataList, Class<T> c, HttpServletResponse response) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet(excelName);
        List<Field> list = filterField(c.getDeclaredFields());
        Row firstRow = sheet.createRow(0);
        int cellLength = list.size();
        for (int j = 0; j < cellLength; j++) {
            Cell cell = firstRow.createCell(j);
            cell.setCellValue(list.get(j).getAnnotation(ExcelCell.class).value());
        }

        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            T t = dataList.get(i);
            for (int j = 0; j < cellLength; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(getGetMethod(t, list.get(j).getName()).toString());
            }
        }

        OutputStream output = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=" + c.getSimpleName() + ".xlsx");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    }

    private int index = 0;
    private int count = 0;

    /**
     * 导入功能（导入的excel单元格要求设置为文本格式）
     *
     * @param excel
     * @param c
     * @return
     * @throws IOException
     * @throws IllegalAccessException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws InstantiationException
     */
    public List<T> importExcel(MultipartFile excel, Class<T> c) throws IOException, IllegalAccessException, NoSuchMethodException, InvocationTargetException, InstantiationException {
        String excelName = excel.getOriginalFilename();
        Workbook wb = null;
        if (excelName.endsWith("xlsx")) {
            wb = new XSSFWorkbook(excel.getInputStream());
        } else if (excelName.endsWith("xls")) {
            wb = new HSSFWorkbook(excel.getInputStream());
        } else {
            throw new FileNotFoundException("文件类型错误");
        }
        List<T> list = new ArrayList<>();
        List<Field> fieldList = filterField(c.getDeclaredFields());

        Iterator<Sheet> sheetIterator = wb.sheetIterator();
        while (sheetIterator.hasNext()) {
            Sheet next = sheetIterator.next();
            if (next.getLastRowNum() == -1) return list;
            Iterator<Row> rowIterator = next.rowIterator();
            rowIterator.next();// 排除第一行
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (row.getLastCellNum() == -1) return list;
                Iterator<Cell> cellIterator = row.cellIterator();
                T t = c.getConstructor().newInstance();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    fieldList.get(index).setAccessible(true);
                    String type = fieldList.get(index).getAnnotation(ExcelCell.class).type();
                    if (cell.getCellType() == CellType.STRING) {
                        String stringCellValue = cell.getStringCellValue();
                        if ("".equals(stringCellValue.trim()))
                            count++;
                        if ("Integer".equals(type)) {
                            fieldList.get(index++).set(t, Integer.valueOf(stringCellValue));
                        } else if ("Double".equals(type)) {
                            fieldList.get(index++).set(t, Double.valueOf(stringCellValue));
                        } else if ("BigDecimal".equals(type)) {
                            fieldList.get(index++).set(t, new BigDecimal(stringCellValue));
                        } else {
                            fieldList.get(index++).set(t, stringCellValue);
                        }
                    } else {
                        count++;
                        index++;
                    }
                }
                if (index != count)
                    list.add(t);
                index = 0;
                count = 0;
            }
        }
        return list;
    }

    /**
     * 根据属性，获取get方法
     *
     * @param obj  对象
     * @param name 属性名
     * @return
     * @throws Exception
     */
    private Object getGetMethod(Object obj, String name) throws Exception {
        Method[] m = obj.getClass().getMethods();
        for (int i = 0; i < m.length; i++) {
            if (("get" + name).equalsIgnoreCase(m[i].getName())) {
                return m[i].invoke(obj);
            }
        }
        return null;
    }

    /**
     * 字段过滤
     *
     * @param fields
     * @return
     */
    private List<Field> filterField(Field[] fields) {
        List<Field> list = new ArrayList<>();
        for (Field field : fields)
            if (field.isAnnotationPresent(ExcelCell.class))
                list.add(field);
        return list;
    }
}
